package com.presalept.manipulatedata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.presalept.polodo.EventSummaryDO;
import com.presalept.polodo.JsonDataProtocolDO;
import com.presalept.polodo.PersonalMainPageDO;

public class RetrieveData {
	
	private List<PersonalMainPageDO> listPmpdAll;
	private String dbJson;

	public String GetPersonalMainPageAll(Integer Userid) throws SQLException
	{

		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select project.projectname, project.projectid, userinfo.username, userinfo.userid, event.eventname, event.eventid,"
				 + " event.createdate, event.mainparticipant, relationofeventanduser.eurelationid"
				 + " from event, relationofeventanduser, project, userinfo"
				 + " where relationofeventanduser.userid = ?"
				 + " and event.eventid = relationofeventanduser.eventid"
				 + " and project.projectid = event.projectidofevent"
				 + " and userinfo.userid = relationofeventanduser.userid"
				 + " and relationofeventanduser.eurelationstatus = 1";
		
		try{
			
			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbPreStmt.setInt(1, Userid);
			
			dbRs = dbPreStmt.executeQuery();	
			
		
			CreatePmpdlistAll(dbRs);
			
			//create jsondata
			
			dbJson = CreateJsonData();
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}

		return dbJson;
	}
	

	private void CreatePmpdlistAll(ResultSet dbRs) throws SQLException{
		

		listPmpdAll = new ArrayList<PersonalMainPageDO>();
		int ProjectId = 0;	
		boolean isPmpdlist = false;
		

		while(dbRs.next()){
			ProjectId = dbRs.getInt("projectid");
			
			//if the projectid of current event is in the list then add event into thats projectdo;
			
			for(int i = listPmpdAll.size() -1; i > -1; i--){
				if(ProjectId == listPmpdAll.get(i).getProjectId()){
					EventSummaryDO eventdo = new EventSummaryDO();  								
					eventdo.setEventId(dbRs.getInt("eventid"));
					eventdo.setEventName(dbRs.getString("eventname"));
					eventdo.setMainparticipant(dbRs.getString("mainparticipant"));
					eventdo.setCreateDate(dbRs.getString("createdate"));				
					listPmpdAll.get(i).getlistEvent().add(eventdo);					
					isPmpdlist = true;												
					break;																		
				}
			}
			
			if(!isPmpdlist){														
				
				PersonalMainPageDO PersonalMainPagedata = new PersonalMainPageDO(); 
				PersonalMainPagedata.setProjectId(dbRs.getInt("projectid"));
				PersonalMainPagedata.setProjectName(dbRs.getString("projectname"));
				PersonalMainPagedata.setUserId(dbRs.getInt("userid"));
				PersonalMainPagedata.setUserName(dbRs.getString("username"));
				
				EventSummaryDO eventDo = new EventSummaryDO();  								
				eventDo.setEventId(dbRs.getInt("eventid"));
				eventDo.setEventName(dbRs.getString("eventname"));
				eventDo.setMainparticipant(dbRs.getString("mainparticipant"));
				eventDo.setCreateDate(dbRs.getString("createdate"));				
				PersonalMainPagedata.getlistEvent().add(eventDo);
				listPmpdAll.add(PersonalMainPagedata);								
				
				isPmpdlist = false;
			}
		}
	}
	
	//create retrieve jsondata

	private String CreateJsonData(){
		
		JsonDataProtocolDO jdpdo = new JsonDataProtocolDO();		
		jdpdo.setPackageCode(1);
		jdpdo.setErrorCode(0);
		jdpdo.setJsonDataObj(listPmpdAll);

		return OperateJsonData.GetJsonData(jdpdo);
		
	}
}
